<?php
    class Subjects extends CI_Model{

        function __construct(){
            parent::__construct();
        }

        function get_subject_list($limit = 0, $offset = 0, $conditions = array()){
            $where = "1 = 1";
            if(!empty($conditions['title'])){
                $where .= " AND s.title LIKE '%{$conditions['title']}%'";
            }

            if(!empty($conditions['tk_category_id'])){
                $where .= " AND s.tk_category_id = '{$conditions['tk_category_id']}'";
            }

            if(!empty($conditions['school_id'])){
                $where .= " AND s.school_id = '{$conditions['school_id']}'";
            }elseif(isset($conditions['school_id']) && $conditions['school_id'] == 0){
                $where .= " AND s.school_id > 0";
            }else{
                $where .= " AND s.school_id = 0";
            }
            $sql = "SELECT SQL_CALC_FOUND_ROWS s.*, c.name, school.school_name FROM {$this->db->dbprefix}b_subject AS s ";
            $sql .= "LEFT JOIN {$this->db->dbprefix}tk_categorys AS c ON(c.id=s.tk_category_id) ";
            $sql .= "LEFT JOIN {$this->db->dbprefix}o2o_school AS school ON(school.id=s.school_id) ";
            $sql .= "WHERE $where ORDER BY s.id DESC ";
            if($limit > 0 || $offset > 0){
               $sql .= "LIMIT $limit, $offset";
            }
            $result = $this->db->query($sql)->result_array();
            $row_num =  $this->db->query('SELECT FOUND_ROWS() as row_num;')->row_array();
            return array("count" => $row_num['row_num'], "result" => $result);
        }

        function get_chapter_list($limit = 0, $offset = 0, $conditions = array()){
            $where = "1 = 1";
            if($conditions){
                if(!empty($conditions['title'])){
                    $where .= " AND c.title LIKE '%{$conditions['title']}%'";
                }

                if(!empty($conditions['subject_id'])){
                    $where .= " AND c.subject_id = '{$conditions['subject_id']}'";
                }

                if(!empty($conditions['parent_id'])){
                    $where .= " AND c.parent_id = '{$conditions['parent_id']}'";
                }
            }
            $sql = "SELECT SQL_CALC_FOUND_ROWS c.*, s.title AS sub_title FROM {$this->db->dbprefix}b_subject_chapter AS c ";
            $sql .= " LEFT JOIN {$this->db->dbprefix}b_subject AS s ON(s.id=c.subject_id) ";
            $sql .= " WHERE $where ORDER BY c.sort ASC ";
            if($limit > 0 || $offset > 0){
               $sql .= "LIMIT $limit, $offset";
            }
            $result = $this->db->query($sql)->result_array();
            $row_num =  $this->db->query('SELECT FOUND_ROWS() as row_num;')->row_array();
            return array("count" => $row_num['row_num'], "result" => $result);
        }
    }